# @Project: diablo
# @File: merchant_stock_related
# @Time: 2023/12/20 19:02
# @User: Jolly
# @Email: jolly@007vin.com

"""
指定公司库存关联
"""

import time
import threading

from pymongo import MongoClient
from pymongo.errors import ConnectionFailure
import pymysql

from dbutils.pooled_db import PooledDB

lock = threading.Lock()


class MerchantStockRelated:

    def __init__(self):

        start_time = time.time()

        # 宏德分公司（除了总仓和一线）
        self.hongde_group = {"e36cf07027ec40aca7be752d45dc84d6": "云汽演示测试",
                                "19752aadc37746fda4023a2bfffa46c1": "宏德龙华总仓一线",
                                "9fd96887306d4594ac85c9bb3404ae98": "龙华鑫众奥",
                                "d7dc91c7d52d4cf79a2795385850babc": "龙岗名捷",
                                "584a3ff537854102aff842c3e34daced": "龙华名诚行",
                                "7813ff2f20d04bd58252b6c18735c392": "罗湖德众",
                                "e086c6ac452645c2822ad836ac28847f": "罗湖名捷",
                                "da88bd366a6049f6b70c5f6395912396": "罗湖名诚行",
                                "0f60506053af4907b901b6d9ca132bb1": "龙岗德众",
                                "59e326e756a641c18c0d58d0df82030e": "龙华名捷",
                                "6d8dc5cc39ea431b81c88e53c213098b": "龙岗名诚行",
                                "23b63424bf3d4a87a51bac7049e7570c": "平湖鑫德众",
                                "9ba515ff52b84ef59370999f133246dc": "平湖名捷",
                                "9b726e87f1ea4255a1378d556cc9f5e2": "平湖名诚行",
                                "f8d1ed3723214e9cb083f89cc4348903": "寮步鑫众奥",
                                "3783bda80d78415cbed62fb88e1cab0d": "寮步路捷",
                                "5b612024616e47858bee94bc019aed7c": "寮步名诚行",
                                "5303f5a79de84e7393e81c8778d69e0e": "沙井鑫众奥",
                                "72a079ec9c4a415aab520d48d52bc1d0": "沙井名捷",
                                "b26d4b19a7004b0fac163409de616d93": "沙井名诚行",
                                "bd86b3f4e2d640a8ba54d81adf50a502": "宝安鑫众奥",
                                "6c124d09c73f4a0e91fb2ec947290cd8": "宝安名捷",
                                "83194859bf1948f3af2c4725bf311ed0": "宝安名诚行",
                                "18967e237c6b400da8b8e2ccd4a2379d": "厚街鑫众奥",
                                "c6184953d5d8406fa58d7c3c713a4ad2": "厚街名捷",
                                "ed2fbe6b0700468bb6c6f93cdf7fc5a8": "厚街名诚行",
                                "91c80181631046be9bfcaec866a4e87a": "塘厦鑫众奥",
                                "b938852583494fada612a7bcb34e43a2": "塘厦名捷",
                                "296e636203a841aa9246fbd630ab259f": "塘厦名诚行"}

        # 初始化数据库连接
        # 2.0生产库
        # self.diablo_conn_2_0 = self.get_mongo_connection("root", "aa123456", "192.168.191.175", "27017")

        # 初始化数据库连接
        self.mysql_conn = self.get_mysql_connection()

        print("========= 数据插入,共耗时:{}'s =========".format(round(time.time() - start_time, 3)))

    # def get_mongo_connection(self, username, password, host, port):
    #     try:
    #         # 创建 MongoClient
    #         client = MongoClient(f"mongodb://{username}:{password}@{host}:{port}",
    #                              maxPoolSize=150,
    #                              minPoolSize=1,
    #                              maxIdleTimeMS=30000,
    #                              waitQueueTimeoutMS=2000)
    #
    #         # 检测连接是否可用，如果连接不可用则抛出异常
    #         if not client:
    #             raise ConnectionFailure("从MongoDB连接池中获取连接失败！")
    #
    #         return client
    #
    #     except ConnectionFailure as e:
    #         # 处理连接失败的异常
    #         print(f"连接MongoDB失败: {e}")
    #         return None

    def get_mysql_connection(self):
        maxconnections = 100  # 最大连接数
        # pool = PooledDB(
        #     pymysql,
        #     maxconnections,
        #     host='192.168.191.21',
        #     user='users',
        #     port=3306,
        #     passwd='uSR@ll7GnJDopei',
        #     db='users',
        #     use_unicode=True)

        pool = PooledDB(
            pymysql,
            maxconnections,
            host='192.168.191.47',
            user='users',
            port=3306,
            passwd='uSR@#H^&JD',
            db='users',
            use_unicode=True)

        return pool

    def check_related(self, merchant_id, bind_merchant_id):
        """
        查看关联是否存在
        """
        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            sql = f"""select * from diablo_merchant_cst_related d where d.merchant_id='{merchant_id}' and d.bind_merchant_id='{bind_merchant_id}' """

            cursor.execute(sql)

            result = cursor.fetchall()

            if len(result) > 0:
                return True
            else:
                return False

        except Exception as e:
            print("获取关联存在失败 - mysql：", e)
        finally:
            mysql_conn.close()

    def insert_data(self):
        """
        插入信息
        """

        mysql_conn = self.mysql_conn.connection()

        try:
            cursor = mysql_conn.cursor()

            for merchant_id, merchant_name in self.hongde_group.items():

                for bind_merchant_id, bind_merchant_name in self.hongde_group.items():
                    # 如果存在关联记录则不写入

                    check = self.check_related(merchant_id, bind_merchant_id)

                    if check:
                        continue

                    key = ['merchant_id', 'merchant_name', 'bind_merchant_id', 'bind_merchant_name',
                           'sort', 'status', 'is_main', 'is_pass']

                    key_param = [merchant_id, merchant_name, bind_merchant_id, bind_merchant_name, 1, 1, 0, 0]

                    value = ""

                    for i, v in enumerate(key):
                        value += '"{}",'.format(key_param[i])

                    values = ", ".join('{0}=values({0})'.format(k) for k in key)

                    try:
                        sql = '''insert into diablo_merchant_cst_related ({}) values({}) ON DUPLICATE KEY UPDATE {} '''.format(
                            ",".join(key), value[:-1], values)
                        # print(sql)
                        cursor.execute(sql)
                        mysql_conn.commit()
                    except Exception as e:
                        pass

        except Exception as e:
            print("写入公司关联信息失败 - mysql：", e)
        finally:
            mysql_conn.close()

        return {}


if __name__ == "__main__":
    service = MerchantStockRelated()
    service.insert_data()